package com.epam.mentoring.music.ui;

import static spark.Spark.*;

import com.epam.mentoring.music.parser.Artists;
import com.epam.mentoring.music.parser.MusicTrack;
import spark.*;
import spark.template.freemarker.FreeMarkerRoute;

import java.sql.*;

import java.util.ArrayList;
import java.util.List;

import java.util.HashMap;
import java.util.Map;

public class WebCharts {

    public static int exit(){
        System.out.println("User triggered application stop.");
        System.exit(0);
        return 0;
    }

   public static void main(String[] args) {

      staticFileLocation("/spark/template/freemarker/"); // TOTO: make static files work from classpath
      //externalStaticFileLocation("C:\\dev\\projects\\q1-2014\\5.Java\\src\\main\\resources\\spark\\template\\freemarker");

      get(new Route("/exit") {
          @Override
          public Object handle(Request request, Response response) {
              exit();
              return 0;
          }
      });


 /* Home page*/
       get(new FreeMarkerRoute("/") {
           @Override
           public ModelAndView handle(Request request, Response response) {
               Map<String, Object> attributes = new HashMap<String, Object>();
               String query = request.queryParams("query");
               List<MusicTrack> searchResults = new ArrayList<MusicTrack>();

               if (query !=null) {

                   System.out.println(query);
                   Connection con = null;
                   Statement st = null;
                   ResultSet rs = null;

                   String url = "jdbc:mysql://mysql2.alwaysdata.com:3306/epamtest_q12014";
                   String user = "epamtest";
                   String password = "Admin;";

                   try {
                       con = DriverManager.getConnection(url, user, password);
                       PreparedStatement prepst = con.prepareStatement(
                               "SELECT tracks.name, artists.name, tracks.cover_url FROM tracks LEFT JOIN artists ON tracks.artist_id = artists.id WHERE tracks.name LIKE ? or artists.name LIKE ? ORDER BY artists.name, tracks.name");
                       prepst.setString(1, "%" + query + "%");
                       prepst.setString(2, "%" + query + "%");
                       rs = prepst.executeQuery();

                       if (!rs.next()) {
                           searchResults.add(new MusicTrack("No Records were found", "", "", "", ""));
                           attributes.put("searchResults", searchResults);

                       } else {
                           searchResults.add(new MusicTrack(rs.getString(1), rs.getString(2), "", rs.getString(3), ""));
                           attributes.put("searchResults", searchResults);
                           while (rs.next()) {
                               searchResults.add(new MusicTrack(rs.getString(1), rs.getString(2), "", rs.getString(3), ""));
                               //System.out.println(result);
                               attributes.put("searchResults", searchResults);
                           }
                       }

                   } catch (SQLException ex) {
                       System.out.println(ex.getMessage());

                   } finally {
                       try {
                           if (rs != null) {
                               rs.close();
                           }
                           if (st != null) {
                               st.close();
                           }
                           if (con != null) {
                               con.close();
                           }

                       } catch (SQLException ex) {
                           System.out.println(ex.getMessage());
                       }
                   }

               } else {
                   searchResults.add(new MusicTrack("", "", "", "", ""));
                   attributes.put("searchResults", searchResults);
               }

               return modelAndView(attributes, "home.ftl");
           }
       });

       /* About page */
       get(new FreeMarkerRoute("/about") {
           @Override
           public ModelAndView handle(Request request, Response response) {
               Map<String, Object> attributes = new HashMap<String, Object>();

               return modelAndView(attributes, "about.ftl");
           }
       });


       /* Tracks page */
       get(new FreeMarkerRoute("/tracks") {
           @Override
           public ModelAndView handle(Request request, Response response) {
               Map<String, Object> attributes = new HashMap<String, Object>();

               List<MusicTrack> trackResults = new ArrayList<MusicTrack>();

               Connection con = null;
               Statement st = null;
               ResultSet rs = null;

               String url = "jdbc:mysql://mysql2.alwaysdata.com:3306/epamtest_q12014";
               String user = "epamtest";
               String password = "Admin;";

               try {
                   con = DriverManager.getConnection(url, user, password);
                   PreparedStatement prepst = con.prepareStatement(
                           "SELECT tracks.name, artists.name, tracks.cover_url FROM tracks LEFT JOIN artists ON tracks.artist_id = artists.id");

                   rs = prepst.executeQuery();

                   while (rs.next()) {
                       trackResults.add(new MusicTrack(rs.getString(1), rs.getString(2), "", rs.getString(3), ""));
                       //System.out.println(result);
                       attributes.put("trackResults", trackResults);

                   }

               } catch (SQLException ex) {
                   System.out.println(ex.getMessage());

               } finally {
                   try {
                       if (rs != null) {
                           rs.close();
                       }
                       if (st != null) {
                           st.close();
                       }
                       if (con != null) {
                           con.close();
                       }

                   } catch (SQLException ex) {
                       System.out.println(ex.getMessage());
                   }
               }

               return modelAndView(attributes, "tracks.ftl");
           }
       });

       /* Artists page */
       get(new FreeMarkerRoute("/artists") {
           @Override
           public ModelAndView handle(Request request, Response response) {
               Map<String, Object> attributes = new HashMap<String, Object>();
               List<Artists> searchResults = new ArrayList<Artists>();

                   Connection con = null;
                   Statement st = null;
                   ResultSet rs = null;

                   String url = "jdbc:mysql://mysql2.alwaysdata.com:3306/epamtest_q12014";
                   String user = "epamtest";
                   String password = "Admin;";

                   try {
                       con = DriverManager.getConnection(url, user, password);
                       PreparedStatement prepst = con.prepareStatement(
                               "SELECT artists.name, artists.genre, artists.comments FROM artists");
                       rs = prepst.executeQuery();

                       while (rs.next()) {
                           searchResults.add(new Artists(rs.getString(1),rs.getString(2),rs.getString(3)));
                           //System.out.println(rs.getString(1));
                           attributes.put("searchResults", searchResults);
                       }
                   } catch (SQLException ex) {
                       System.out.println(ex.getMessage());
                   } finally {
                       try {
                           if (rs != null) {
                               rs.close();
                           }
                           if (st != null) {
                               st.close();
                           }
                           if (con != null) {
                               con.close();
                           }
                       } catch (SQLException ex) {
                           System.out.println(ex.getMessage());
                       }
                   }
               return modelAndView(attributes, "artists.ftl");
           }
       });
   }
}